<?php include '../../checkPCandVPC.php'; ?>
<?php include 'top.html'; ?>
<?php include '../../authorization_check.php'; ?>

<?php
// Test CVS

require_once 'Excel\reader.php';
require_once '..\db\DBConnectionManager.php';



//all the variables for the related fields
$student_ID;
$department;
$name;
$surname;
$section_ID;
$course_ID;
$instructor_ID;
$title;
$hour;
$bank;
$iname;
$isurname;

// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();


$filename = $_FILES['userfile']['name'];

//echo "filename : "."$filename"."\n";

// Set output Encoding.
$data->setOutputEncoding('Latin5');
//$data->setOutputEncoding('CP1251');

/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb');
*
**/

/***
* By default rows & cols indeces start with 1
* For change initial index use:
* $data->setRowColOffset(0);
*
**/



/***
*  Some function for formatting output.
*/

$data->setDefaultFormat('%d');

/* setDefaultFormat - set format for columns with unknown formatting
*
* $data->setColumnFormat(4, '%.3f');
* setColumnFormat - set format for column (apply only to number fields)
*
**/


$data->read($filename);
//echo "data : "."$data"."\n";

/*
 $data->sheets[0]['numRows'] - count rows
 $data->sheets[0]['numCols'] - count columns
 $data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column

 $data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
    
    $data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
        if 'type' == "unknown" - use 'raw' value, because  cell contain value with format '0.00';
    $data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format 
    $data->sheets[0]['cellsInfo'][$i][$j]['colspan'] 
    $data->sheets[0]['cellsInfo'][$i][$j]['rowspan'] 
*/

//echo "numCols:".$data->sheets[0]['numCols']."\n";
//echo "numRows:".$data->sheets[0]['numRows']."\n";

error_reporting(E_ALL ^ E_NOTICE);

for ($i = 1; $i < 2; $i++) {  // [''numRows'']
	for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { // ['numCols']
		//echo "".$data->sheets[0]['numRows']."\n";
		//echo "".$data->sheets[0]['numCols']."\n";
			/*if( $j % $data->sheets[0]['numCols'] == 0 ) {
				echo ""."<b>".$data->sheets[0]['cells'][$i][$j]."</b>"."\t"."<br>";				
			}
			else{
				echo ""."<b>".$data->sheets[0]['cells'][$i][$j]."\t"."</b>";  // echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
				}
	*/	
	
	if( $j == 1 ){
				$var1 = $data->sheets[0]['cells'][$i][1];
				//echo $var1;
				}
			else if( $j == 2 ){
				$var2 = $data->sheets[0]['cells'][$i][2];
				//echo $var2;
				}
			else{
				$var3 = $data->sheets[0]['cells'][$i][3];
				//echo $var3;
				}
			
	}
			
			
}

//preperation for switch case 

if( $var1 == "stu_ID" && $var2 == "department" && $var3 == "name" )
		$caseNo = 1;
else if( $var1 == "inst_sicil_no" && $var2 == "department" && $var3 == "name" )
		$caseNo = 2;
else if( $var1 == "semester_ID" && $var2 == "section_no" && $var3 == "student_ID" )
		$caseNo = 3;
else if( $var1 == "course code" && $var2 == "name of the course" && $var3 == "credit" )
		$caseNo = 4;
else if( $var1 == "course code" && $var2 == "section no" && $var3 == "inst_sicil_no" )
		$caseNo = 5;
else if ( $var1 == "stu_ID" && $var2 == "total payment" && $var3 == "bank" )
		$caseNo = 6;
		
		
switch ($caseNo)
{
case '1' :

for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {  // [''numRows'']
	for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { // ['numCols']
		//echo "".$data->sheets[0]['numRows']."\n";
		//echo "numRows: ".$data->sheets[0]['numRows']."<br>";
		//echo "-----------------"."<b>";
			
			
			/**********************
			
			
			if( $j % $data->sheets[0]['numCols'] == 0 ) {
					
				//echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t"."<br>";				
			}
			else{
				echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t";  // echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
				}
			
			
			***********************/
			if( $j == 1 )
				$student_ID = $data->sheets[0]['cells'][$i][1];
			else if( $j == 2 ){
				$department = $data->sheets[0]['cells'][$i][2];
					if($department == "IS")
						$dvalue = 9010;
					else if($department == "COGS")
						$dvalue = 9020;
					else if($department == "MODSIM")
						$dvalue = 9030;
					else if($department == "ION")
						$dvalue = 9040;
					else if($department == "SM")
						$dvalue = 9050;	
					else if($department == "MIN")
						$dvalue = 9060;
					else if($department == "WBL")
						$dvalue = 9070;
					else
						echo "USTA BI YANLISLIK OLMASIN BU KAYITLARDA!!!";
						}
			else if( $j == 3 )
				$name = $data->sheets[0]['cells'][$i][3];
			else if( $j == 4 )
				$surname = $data->sheets[0]['cells'][$i][4];	
				
	//echo "dvalue : ".$dvalue;		
			
	}
	
	/*************************************
	//mysql_connect("http://localhost:3306","root","root") or die ('I can\'t connect to the db because'.mysql_error());
	
	//mysql_select_db("isproje");

	
	//$query = "INSERT INTO student SET studentID = '$student_ID', programID = '$dvalue', FirstName = '$name', LastName = '$surname'";
											
	//$query = "UPDATE student SET programID = '$dvalue',FirstName = '$name' AND LastName = '$surname' WHERE studentID = '$student_ID'";
	//$result = mysql_query($query);
	
	//$num_results = mysql_num_rows($result);
	*************************************/
	
	$con = mysql_connect("localhost","is502","is502");
	if (!$con)
	  {
	  die('Could not connect: ' . mysql_error());
	  }

	mysql_select_db("iipis_budget", $con);

	// CALISAN KODA YILAN BILE DOKUNMAZ!!!
	//mysql_query("INSERT INTO student SET studentID = '$student_ID' , LastName = '$surname', FirstName = '$name', programID= '$dvalue' ");
	//mysql_query("UPDATE student SET studentID = '$student_ID' , LastName = '$surname', FirstName = '$name', programID= '$dvalue' ");
	
	
	mysql_query("INSERT INTO student SET studentID = '$student_ID' , programCode= '$dvalue', lastName = '$surname', firstName = '$name' ON DUPLICATE KEY UPDATE studentID = '$student_ID' , programCode= '$dvalue', lastName = '$surname', firstName = '$name'");
	
	mysql_close($con);
	
	/*
	echo "".$student_ID.",";
	echo "".$department.",";
	echo "".$name.",";
	echo "".$surname."<br>";
  */
}

echo "<b>"." THE STUDENT RECORDS ARE SUCCESFULLY INSERTED INTO THE DATABASE ;) "."<b>";
break;

case '2' :

for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {  // [''numRows'']
	for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { // ['numCols']
		//echo "".$data->sheets[0]['numRows']."\n";
		//echo "".$data->sheets[0]['numCols']."\n";
			
			
			/**********************
			
			
			if( $j % $data->sheets[0]['numCols'] == 0 ) {
					
				echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t"."<br>";				
			}
			else{
				echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t";  // echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
				}
			
			
			***********************/
			if( $j == 1 )
				$instructor_ID = $data->sheets[0]['cells'][$i][1];
			else if( $j == 2 ){
				$department = $data->sheets[0]['cells'][$i][2];
					if($department == "IS"){
						$dvalue = 9010;
						//$dname = "Information Systems";
						}
					else if($department == "COGS"){
						$dvalue = 9020;
						//$dname = "Cognitive Sciences";
						}
					else if($department == "MODSIM"){
						$dvalue = 9030;
						//$dname = "Modelling And Simulation";
						}
					else if($department == "ION"){
						$dvalue = 9040;
						//$dname = "Informatics Online";
						}
					else if($department == "SM"){
						$dvalue = 9050;	
						//$dname = "Software Management";
						}
					else if($department == "MIN"){
						$dvalue = 9060;
						//$dname = "Medical Informatics";
						}
					else if($department == "WBL"){
						$dvalue = 9070;
						//$dname = "Work Based Learning Studies";
						}
					else
						echo "USTA BI YANLISLIK OLMASIN BU KAYITLARDA!!!";
						}
			else if( $j == 3 )
				$iname = $data->sheets[0]['cells'][$i][3];
			else if( $j == 4 )
				$isurname = $data->sheets[0]['cells'][$i][4];
			else if( $j == 5 )
				$title = $data->sheets[0]['cells'][$i][5];
	}
	
	/*************************************
	//mysql_connect("http://localhost:3306","root","root") or die ('I can\'t connect to the db because'.mysql_error());
	
	//mysql_select_db("isproje");

	
	//$query = "INSERT INTO student SET studentID = '$student_ID', programID = '$dvalue', FirstName = '$name', LastName = '$surname'";
											
	//$query = "UPDATE student SET programID = '$dvalue',FirstName = '$name' AND LastName = '$surname' WHERE studentID = '$student_ID'";
	//$result = mysql_query($query);
	
	//$num_results = mysql_num_rows($result);
	*************************************/
	
	$con = mysql_connect("localhost","is502","is502");
	if (!$con)
	  {
	  die('Could not connect: ' . mysql_error());
	  }

	mysql_select_db("iipis_budget", $con);
	
	//mysql_query("INSERT INTO instructor SET instructorID = '$instructor_ID', programID = '$dvalue', FirstName = '$iname', LastName = '$isurname', //Title = '$title'");

	//mysql_query("INSERT INTO student SET studentID = '$student_ID' , LastName = '$surname', FirstName = '$name', programID= '$dvalue' ON //DUPLICATE 	KEY UPDATE studentID = '$student_ID' , LastName = '$surname', FirstName = '$name', programID= '$dvalue'");
	
	mysql_query("INSERT INTO instructor SET instructorID = '$instructor_ID' , lastname = '$isurname',firstname = '$iname', 	programCode = '$dvalue', title = '$title' ON DUPLICATE KEY UPDATE instructorID = '$instructor_ID', 	firstname = '$iname', lastname = '$isurname', programCode = '$dvalue', title = '$title' ");
	
	//echo mysql_errno($link) . ": " . mysql_error($link). "\n";
	mysql_close($con);
	
	/*
	echo "".$instructor_ID." ";
	echo "".$department." ==> ". "$dname"." ";
	echo "".$iname." ";
	echo "".$isurname." ";
	echo "".$title." "."<br>";
  */
}
echo "<b>"." THE INSTRUCTOR RECORDS ARE SUCCESFULLY INSERTED INTO THE DATABASE ;) "."<b>";
break;

case '3' :

for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {  // [''numRows'']
	for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { // ['numCols']
		//echo "".$data->sheets[0]['numRows']."\n";
		//echo "".$data->sheets[0]['numCols']."\n";
			
			
			/**********************
			
			
			if( $j % $data->sheets[0]['numCols'] == 0 ) {
					
				//echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t"."<br>";				
			}
			else{
				echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t";  // echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
				}
			
			
			***********************/
			
			if( $j == 1 )
				$semester_ID = $data->sheets[0]['cells'][$i][1];
			else if( $j == 2 )
				$section_ID = $data->sheets[0]['cells'][$i][2];
			else if( $j == 3 ) 
				$student_ID  = $data->sheets[0]['cells'][$i][3];
	}
	
		$con = mysql_connect("localhost","is502","is502");
		if (!$con)
		{
		die('Could not connect: ' . mysql_error());
		}

		mysql_select_db("iipis_budget", $con);
	
	
	mysql_query("INSERT INTO enrollment SET semesterID = '$semester_ID' , sectionID = '$section_ID', studentID = '$student_ID' ON DUPLICATE KEY UPDATE semesterID = '$semester_ID' , sectionID = '$section_ID', studentID = '$student_ID'");
	
	mysql_close($con);
	/*
	echo "".$semester_ID.",";
	echo "".$section_ID.",";
	echo "".$student_ID.",";
  */
}

break;
echo "<b>"." THE STUDENT-COURSES RECORDS ARE SUCCESFULLY INSERTED INTO THE DATABASE "."<b>";
case '4' :

for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {  // [''numRows'']
	for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { // ['numCols']
		//echo "".$data->sheets[0]['numRows']."\n";
		//echo "".$data->sheets[0]['numCols']."\n";
			
			
			/**********************
			
			
			if( $j % $data->sheets[0]['numCols'] == 0 ) {
					
				//echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t"."<br>";				
			}
			else{
				echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t";  // echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
				}
			
			
			***********************/
			if( $j == 1 )
				$course_ID = $data->sheets[0]['cells'][$i][1];
			else if( $j == 2 )
				$course_name = $data->sheets[0]['cells'][$i][2];
			else if( $j == 3 ) 
				$credit = $data->sheets[0]['cells'][$i][3];
	}
	
	
		$con = mysql_connect("localhost","is502","is502");
		if (!$con)
		{
		die('Could not connect: ' . mysql_error());
		}

		mysql_select_db("iipis_budget", $con);
	
	
	mysql_query("INSERT INTO course SET courseCode = '$course_ID' , courseName = '$course_name', credit = '$credit' ON DUPLICATE KEY UPDATE courseCode = '$course_ID' , courseName = '$course_name', credit = '$credit'");
	
	mysql_close($con);
	/*
	echo "".$course_ID.",";
	echo "".$course_name.",";
	echo "".$credit.",";
  */
}
echo "<b>"." THE COURSE RECORDS ARE SUCCESFULLY INSERTED INTO THE DATABASE ;) "."<b>";
break;

case '5' :

for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {  // [''numRows'']
	for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { // ['numCols']
		
		
		//echo "".$data->sheets[0]['numRows']."\n";
		//echo "".$data->sheets[0]['numCols']."\n";
			
			
			/**********************
			
			
			if( $j % $data->sheets[0]['numCols'] == 0 ) {
					
				//echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t"."<br>";				
			}
			else{
				echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t";  // echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
				}
			
			
			***********************/
			$semesterID = 20081;
			
			if( $j == 1 )
				$course_ID = $data->sheets[0]['cells'][$i][1];
			else if( $j == 2 )
				$section_ID = $data->sheets[0]['cells'][$i][2];
			else if( $j== 3)
				$instructor_ID = $data->sheets[0]['cells'][$i][3];
	}
	
	
	
		$con = mysql_connect("localhost","is502","is502");
		if (!$con)
		{
		die('Could not connect: ' . mysql_error());
		}

		mysql_select_db("iipis_budget", $con);
	
		mysql_query("INSERT INTO section SET semesterID = $_SESSION[semesterID], sectionID = '$section_ID', courseCode = '$course_ID', instructorID = '$instructor_ID' ON DUPLICATE KEY UPDATE sectionID = '$section_ID', courseCode = '$course_ID', instructorID = '$instructor_ID'");
		
		
	//mysql_query("INSERT INTO course SET courseID = '$course_ID' , sectionID = '$section_ID', instructorID = '$instructor_ID' ON DUPLICATE KEY //UPDATE courseID = '$course_ID' , sectionID = '$section_ID', instructorID = '$instructor_ID' ");
	
	mysql_close($con);	
	/*
	echo "".$course_ID.",";
	echo "".$section_ID.",";
	echo "".$instructor_ID."<br>";
  */
}
echo "<b>"." THE SECTION RECORDS ARE SUCCESFULLY INSERTED INTO THE DATABASE ;) "."<b>";
break;

case '6' :

for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {  // [''numRows'']
	for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { // ['numCols']
		//echo "".$data->sheets[0]['numRows']."\n";
		//echo "".$data->sheets[0]['numCols']."\n";
			
			
			/**********************
			
			
			if( $j % $data->sheets[0]['numCols'] == 0 ) {
					
				//echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t"."<br>";				
			}
			else{
				echo "".$data->sheets[0]['cells'][$i][$j]."\t"."\t";  // echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
				}
			
			
			***********************/
			if( $j == 1 )
				$student_ID = $data->sheets[0]['cells'][$i][1];
			else if( $j == 2 )
				$payment = $data->sheets[0]['cells'][$i][2];
			else if( $j == 3 )
				$bank = $data->sheets[0]['cells'][$i][3];
				echo "bank:".$bank."<br>";
				
	}
	
		$con = mysql_connect("localhost","is502","is502");
		if (!$con)
		{
		die('Could not connect: ' . mysql_error());
		}

		mysql_select_db("iipis_budget", $con);
	
	
    $sql = "SELECT spID FROM studentpayment WHERE spID = (SELECT MAX(spID) FROM studentpayment)";
    $result = mysql_query($sql);
    $row = mysql_fetch_row($result);
    $max_id = $row[0];
    
    echo "maxid:".$max_id."<br>";
    $max_id+=1;
    echo "maxid:".$max_id."<br>";
    
    $sql = "SELECT bankID FROM bank WHERE name = '$bank'";
    $result = mysql_query($sql);
    $row = mysql_fetch_row($result);
    $bankID = $row[0];
    $bankID++;
    echo "bankID:".$bankID."<br>";
    
    mysql_query("INSERT INTO studentpayment 
                  SET semesterID = $_SESSION[semesterID],
                  spID = '$max_id',
                  bankID = '$bankID',
                  studentID = '$student_ID' ,
                  amount = '$payment'
                  ON DUPLICATE KEY UPDATE
                  spID = '$max_id',
                  bankID = '$bankID',
                  studentID = '$student_ID' ,
                  amount = '$payment'
                  ");
                      
		
	  //mysql_query("INSERT INTO course SET courseID = '$course_ID' , sectionID = '$section_ID', instructorID = '$instructor_ID' ON DUPLICATE KEY //UPDATE courseID = '$course_ID' , sectionID = '$section_ID', instructorID = '$instructor_ID' ");
	 
	  mysql_close($con);	
	/*
	echo "".$student_ID.",";
	echo "".$payment.",";
	echo "".$bankID."";
  */
}
echo "<b>"." THE STUDENT PAYMENT RECORDS ARE SUCCESFULLY INSERTED INTO THE DATABASE ;) "."<b>";
break;

default :
	echo '<b>ERROR?</b>';

}
//print_r($data);
//print_r($data->formatRecords);
?>

